package com.dhf.framework.common;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
* @ClassName: GenerateSql
* @Description: 生成mybatis查询sql语句
* @author HoraceHo
*
 */
public class GenerateSqlUtil {

	private static Logger LOGGER = LoggerFactory.getLogger(GenerateSqlUtil.class);

	/**
	 * 
	* @Title: generateSelectSql
	* @Description: 生成查询sql语句
	* @param tableName 表名
	* @param selectList 要查询字段
	* @return String   sql语句 
	 */
	public static String generateSelectSql(String tableName,List<String> selectList){
		StringBuffer sb = new StringBuffer(" SELECT ");
		if(CollectionUtils.isNotEmpty(selectList)){
			StringBuffer selectFieldNames = new StringBuffer();
			for(String fieldName:selectList){
				selectFieldNames.append(fieldName).append(",");
			}
			sb.append(selectFieldNames.substring(0, selectFieldNames.length()-1));
		}else{
			sb.append("*");
		}
		sb.append("  FROM  ").append(tableName);
		return sb.toString();
	}


	public static String generateCountSql(String tableName){
		StringBuffer sb = new StringBuffer("SELECT count(*) ");
		sb.append(" FROM ").append(tableName);
		return sb.toString();
	}
	
	/**
	 * 
	* @Title: generateAddSql
	* @Description: 生成新增sql语句
	* @param tableName 表名
	* @param addMap  表字段值集
	* @return String    sql语句
	 */
	public static String generateAddSql(String tableName,Map<String, Object> addMap){
		StringBuffer sb=new StringBuffer(" INSERT INTO ");
		sb.append(tableName);
		sb.append("(");
		StringBuffer fields=new StringBuffer();
		StringBuffer values=new StringBuffer();
		Iterator<String> it = addMap.keySet().iterator();
		while (it.hasNext()) {
			String key = it.next();
			Object value=MapUtils.getObject(addMap, key);
			fields.append(key).append(',');
			values.append("'"+value+"',");
		}
		sb.append(fields.substring(0, fields.length()-1));
		sb.append(" )values( ");
		sb.append(values.substring(0, values.length()-1));
		sb.append(" ) ");
		return sb.toString();
	}
	
	
	/**
	 * 
	* @Title: generateUpdateSql
	* @Description: 生成更新sql语句
	* @param tableName 表名
	* @param updateMap  表字段值集
	* @return String    sql语句
	 */
	public static String generateUpdateSql(String tableName,Map<String, Object> updateMap,String tablePrimarykey){
		String tablePrimaryValue=MapUtils.getString(updateMap, tablePrimarykey);
		updateMap.remove(tablePrimarykey);
		StringBuffer sb=new StringBuffer(" UPDATE ");
		sb.append(tableName);
		sb.append(" SET ");
		Iterator<String> it = updateMap.keySet().iterator();
		StringBuffer fieldAndValues=new StringBuffer();
		while (it.hasNext()) {
			String key = it.next();
			Object value=MapUtils.getObject(updateMap, key);
			if(value != null ){
				fieldAndValues.append(key).append(" ='").append(value).append("',");
			}
		}
		sb.append(fieldAndValues.substring(0, fieldAndValues.length()-1));
		sb.append(" where "+tablePrimarykey+"= '").append(tablePrimaryValue).append("'");
		return sb.toString();
	}
	
	
	/**
	 * 
	* @Title: generateDeleteSql
	* @Description: 生成删除sql语句
	* @param tableName 表名
	* @param tablePrimaryKey  表id
	* @return String    sql语句
	 */
	public static String generateDeleteSql(String tableName,String tablePrimaryKey,String tablePrimaryValue){
		StringBuffer sb=new StringBuffer(" DELETE FROM ");
		sb.append(tableName);
		sb.append(" where "+tablePrimaryKey+"='").append(tablePrimaryValue).append("'");
		return sb.toString();
	}
	
	
	/**
	 * 直接拼接参数
	 * 
	 * @author dhf
	 * @param searchParams 查询参数
	 * @return
	 */
	public static String  generateWhereSql(Map<String, Object> searchParams) {
		StringBuffer sqlBuffer = new StringBuffer();
		if(MapUtils.isNotEmpty(searchParams)) {
			sqlBuffer.append(" where ");
			for (String key : searchParams.keySet()) {
				sqlBuffer.append(" ").append(key).append(" = ").append('\'')
				.append(searchParams.get(key).toString().replaceAll("'", ""))
				.append("\' ")
				.append(" and");
			}
			sqlBuffer.delete(sqlBuffer.length()-3,sqlBuffer.length());
		}
		return sqlBuffer.toString();
	}


	public static Map<String,Object> filteFieldNames(List<String> fieldNames,Map<String,Object> searchParams){
		List<String> removeKeyList=new ArrayList<String>();
		for (String key : searchParams.keySet()) {
			if(!fieldNames.contains(key)){
				removeKeyList.add(key);
			}
		}
		for(String removeKey:removeKeyList){
			searchParams.remove(removeKey);
		}
		return searchParams;
	}

	public static void main(String[] args) {
		Map<String, Object> searchParams=new HashMap<String, Object>();
//		searchParams.put("skip","0");
//		searchParams.put("take","10");
//		searchParams.put("page","1");
//		searchParams.put("pageSize","10");
		List<String> fieldNames=new ArrayList<String>();
		fieldNames.add("pageSize");
		System.out.println(generateWhereSql(searchParams));
		filteFieldNames(fieldNames,searchParams);
		System.out.println(searchParams);
	}
	/**
	 * 
	* @Title: generateLimitSql
	* @Description: 生成limit sql语句
	* @param  page
	* @param  pageSize
	* @return String    返回类型
	* @throws
	 */
	public static String  generateLimitSql(int page,int pageSize) {
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" limit ").append((page - 1) * pageSize).append(',').append(pageSize);
		return sqlBuffer.toString();
	}
	
	
	/**
	 * 
	* @Title: generateOrderSql
	* @Description: 生成Order sql语句 如：ORDER BY Company DESC, OrderNumber ASC
	* @param  map  排序字段以及排序方式
	* @return String    返回类型
	* @throws
	 */
	public static String  generateOrderSql(Map<String,Object> map) {
		StringBuffer sqlBuffer = new StringBuffer();
		if(MapUtils.isNotEmpty(map)){
			sqlBuffer.append(" order by ");
			Iterator<String> it = map.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				Object value=MapUtils.getObject(map, key);
				if(value != null ){
					sqlBuffer.append(key+"  "+value+",");
				}else{
					LOGGER.error("字段："+key+"没有设置排序方式");
				}
			}
		}
		
		return sqlBuffer.toString();
	}
	
}
